package com.forekenow.Demo14;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * JDBC工具类
 */
public class DBUtil {
    /**
     * 连接数据库方法
     */
    public Connection getConnection() {
        //1.加载驱动    jdbc驱动类：com.mysql.jdbc.Driver 8.0改成com.mysql.cj.jdbc.Driver
        //2.获取连接
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/basic5?useSSL=false&useUnicode=true&characterEncoding=utf-8",
                    "root", "root");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 查询
     */
    public List<GuestBook> select(Connection conn) {
        List<GuestBook> list = new ArrayList<>();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from guestbook ");
            while (rs.next()) {
                GuestBook gb = new GuestBook();
                gb.setId(rs.getInt("id"));
                gb.setName(rs.getString("name"));
                gb.setPhone(rs.getString("phone"));
                gb.setEmail(rs.getString("email"));
                gb.setTitle(rs.getString("title"));
                gb.setContent(rs.getString("content"));
                list.add(gb);

            }
            rs.close();
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * DML（insert delete update）操作
     */

    public void insert(Connection conn) {
        try {
            PreparedStatement pre = conn.prepareStatement("insert into guestbook(name,phone,email,title,content)value (?,?,?,?,?)");
            System.out.println("请分别输入姓名、电话、邮箱、标题、内容");
            Scanner scanner = new Scanner(System.in);
            String name = scanner.next();
            Scanner scanner1 = new Scanner(System.in);
            String phone = scanner1.next();
            Scanner scanner2 = new Scanner(System.in);
            String email = scanner2.next();
            Scanner scanner3 = new Scanner(System.in);
            String title = scanner3.next();
            Scanner scanner4 = new Scanner(System.in);
            String content = scanner4.next();
            pre.setString(1, name);
            pre.setString(2, phone);
            pre.setString(3, email);
            pre.setString(4, title);
            pre.setString(5, content);
            pre.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void delete(Connection conn, int id) throws SQLException {
        PreparedStatement pstmt1 = conn.prepareStatement("delete from guestbook where id = ?");
        pstmt1.setString(1, String.valueOf(id));
        pstmt1.executeUpdate();
    }

    public void update(Connection conn,int phone,int id) throws SQLException {
        PreparedStatement pstmt2 = conn.prepareStatement("update guestbook set phone =? where id = ?");
        pstmt2.setString(1, String.valueOf(phone));
        pstmt2.setString(2, String.valueOf(id));
        pstmt2.executeUpdate();
    }
}
